package dao;

import bean.Area;
import util.JDBCutil;
import util.SQLUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by young on 2017/6/24.
 */
public class AreaDao {
    private Connection connection = JDBCutil.getConnection();

    public List<Area> areaList() {
        List<Area> list = new ArrayList<>();
        String sql = "SELECT * FROM area";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                Area area = new Area();
                area.setId(set.getInt("id"));
                area.setName(set.getString("name"));
                list.add(area);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addArea(Area area) {
        int row = 0;
        String sql = "INSERT INTO area (name) VALUES (?);";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, area.getName());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }


    public Area getArea(int id) {
        String sql = "SELECT * FROM area WHERE id=?";
        Area area = new Area();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                area.setId(set.getInt("id"));
                area.setName(set.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return area;
    }

    public int deleteArea(int id) {
        String sql1 = "DELETE FROM goods WHERE goods.area_id=area.id OR people.area_id=area.id AND id=?";

        try {
            PreparedStatement ps = connection.prepareStatement(sql1);
            ps.setInt(1, id);
            ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "DELETE FROM area WHERE id=?";
        int row;
        row = SQLUtil.deleteInfo(sql, id);
        return row;
    }

    public int updateArea(Area area) {
        int row = 0;
        String sql = "UPDATE area SET name = ? WHERE id=? ;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, area.getName());
            ps.setInt(2, area.getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }


}
